package com.project.dao.impl;

import com.project.bean.CutPageBean;
import com.project.bean.RoomBean;
import com.project.dao.IRoomDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
@Repository
public class RoomDaoImpl extends BaseDao implements IRoomDao {
    @Override
    public CutPageBean<RoomBean> findByItem(Integer pageNo, String roomName, String roomState, String into, String roomSex) {
        CutPageBean<RoomBean> cut = new CutPageBean<>();
        this.setConnection();
        String sql = "SELECT r.*,COUNT(s.`fk_roomId`)c FROM t_room r LEFT JOIN t_student s ON s.`fk_roomId`=r.`pk_roomId` where 1=1 ";
        String dynaSql = "";
        String dyna1 = "";
        if (roomName != null && roomName.length() != 0){
            dynaSql += "AND r.`t_roomName` LIKE '%"+roomName+"%' ";
        }
        if (roomState!= null && roomState.length() != 0){
            dynaSql += "AND r.`t_roomState` LIKE '%"+roomState+"%' ";
        }
        if (roomSex != null && roomSex.length() !=0){
            dynaSql += "AND r.`t_roomSex` LIKE '%"+roomSex+"%' ";
        }
        if (into!=null&& into.length()!=0){
            dyna1+="having c <r.`t_roomMaxNumber`";
        }
        try {
            ps = con.prepareStatement(sql+dynaSql+" GROUP BY r.`pk_roomId` "+dyna1+" LIMIT ?,?");
            ps.setObject(1,(pageNo-1)*CutPageBean.PAGESIZE);
            ps.setObject(2,CutPageBean.PAGESIZE);
            rs = ps.executeQuery();
            while (rs.next()){
                RoomBean roomBean = this.getRoom();
                roomBean.setRoomNumbers(rs.getInt("c"));
                cut.getList().add(roomBean);
            }
            ps = con.prepareStatement("select count(*)num from ("+sql+dynaSql+" GROUP BY r.`pk_roomId` "+dyna1+")c");
            rs = ps.executeQuery();
            while (rs.next()){
                cut.setCount(rs.getInt("num"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
        return cut;
    }

    @Override
    public RoomBean findById(Integer id) {
        this.setConnection();
        RoomBean roomBean=null;
        try {
            ps = con.prepareStatement("SELECT r.*,COUNT(s.`fk_roomId`)c FROM t_room r LEFT JOIN t_student s ON s.`fk_roomId`=r.`pk_roomId` where r.`pk_roomId`=? ");
            ps.setObject(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                roomBean=this.getRoom();
                roomBean.setRoomNumbers(rs.getInt("c"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
        return roomBean;
    }

    @Override
    public void del(Integer id) {
        this.setConnection();
        try {
            ps =con.prepareStatement("delete from t_room where pk_roomId=?");
            ps.setObject(1,id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public void add(RoomBean room) {
        this.setConnection();
        try {
            ps = con.prepareStatement("insert into t_room(t_roomName,t_roomState,t_roomMaxNumber,t_roomSex) values(?,?,?,?)");
            ps.setObject(1,room.getRoomName());
            ps.setObject(2,room.getRoomState());
            ps.setObject(3,room.getRoomMaxNumber());
            ps.setObject(4,room.getRoomSex());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public List<RoomBean> findBySex(String sex) {
        this.setConnection();
        List<RoomBean> list  = new ArrayList<>();
        try {
            ps = con.prepareStatement("SELECT r.*,COUNT(s.`fk_roomId`)c FROM t_room r LEFT JOIN t_student s ON s.`fk_roomId`=r.`pk_roomId` where r.`t_roomSex`=? " +
                    "GROUP BY r.`pk_roomId` having c <r.`t_roomMaxNumber`");
            ps.setObject(1,sex);
            rs = ps.executeQuery();
            while (rs.next()){
                list.add(this.getRoom());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
        return list;
    }

    @Override
    public void updateRoom(Integer id, String roomStatus) {
        this.setConnection();
        try {
            ps = con.prepareStatement("update t_room set t_roomState=? where pk_roomId=?");
            ps.setObject(1,roomStatus);
            ps.setObject(2,id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }

    }
    public RoomBean getRoom(){
        RoomBean roomBean = new RoomBean();
        try {
            roomBean.setId(rs.getInt("pk_roomId"));
            roomBean.setRoomName(rs.getString("t_roomName"));
            roomBean.setRoomSex(rs.getString("t_roomSex"));
            roomBean.setRoomState(rs.getString("t_roomState"));
            roomBean.setRoomMaxNumber(rs.getInt("t_roomMaxNumber"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return roomBean;
    }

    public static void main(String[] args) {
        IRoomDao dao = new RoomDaoImpl();
//        System.out.println(dao.findByItem(1,"双","正常","","男"));
//        System.out.println(dao.findById(1));
//        dao.add(new RoomBean("d","男",10));
//        dao.del(7);
//        System.out.println(dao.findBySex("男"));
        dao.updateRoom(4,"正常");
    }
}
